﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace IISLogAnalytics
{
    public class DBO
    {
        public static DataTable getSiteList()
        {
            string sql = @"select [domain],[sitename] from [sitedomain] order by [groupid],[domain] asc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getIPList(string table,int num)
        {
            if (num < 0) throw new Exception("输入数量必须为0或正整数");
            string sql = @"select " + (num == 0 ? "" : ("top " + num)) + " [clientip],sum([Recordcount]) as mycount from [" + table + "] group by [clientip] order by mycount desc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getSiteName()
        {
            string sql = @"select [keywords],[sitename] from [sitename]";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getServerType()
        {
            string sql = @"select [stype],[keywords],[sitename] from [servertype]";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getAnalybyIP(string startdate, string enddate, string sitename, List<string> stype, int num)
        {
            string sql = @"select top " + num + " [clientip] as 客户端IP,sum([ipcount]) as 访问次数,[iparea] as 国家地区,[ipaddress] as 详细地址 from [AnalybyIP] where [sitename]='" + sitename + "' and [stype] in(" + string.Join(",", stype.ToArray()) + ") and [date]>='" + startdate + "' and [date]<='" + enddate + "' group by [clientip],[iparea],[ipaddress] order by 访问次数 desc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getAnalybyPage(string startdate, string enddate, string sitename, List<string> stype, int num)
        {
            string sql = @"select top " + num + " a.[url] as 页面,sum([pagecount]) as 访问次数,b.[comment] as 备注 from [AnalybyPage] a left join [PageComment] b on a.[sitename]=b.[sitename] and a.[url]=b.[uri] where a.[sitename]='" + sitename + "' and a.[stype] in(" + string.Join(",", stype.ToArray()) + ") and a.[date]>='" + startdate + "' and a.[date]<='" + enddate + "' group by a.[url],b.[comment] order by 访问次数 desc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getAnalybyGeneral(string startdate, string enddate, string sitename)
        {
            string sql = "select [date] as 日期,[pvcounttel] as 电信PV量,[pvcountuni] as 联通PV量,[pagecounttel] as 电信独立页面,[pagecountuni] as 联通独立页面,[ipcounttel] as 电信独立访问IP,[ipcountuni] as 联通独立访问IP from [AnalybyGeneral] where [sitename]='" + sitename + "' and [date]>='" + startdate + "' and [date]<='" + enddate + "' order by [date] desc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static DataTable getPageCommentList(string sitename,string domain)
        {
            string sql = "select '" + domain + "' as domain,[uri],[comment] from [PageComment] where [sitename]='" + sitename + "'";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            return dt;
        }

        public static int AddRecordbyIP()
        {
            string sql = @"insert into [AnalybyIP_temp] select replace(newid(),'-',''), [LogFilename],[sitename],[ldate],[clientip],sum([recordcount]),[iparea],[ipaddress],[stype] from [Analy_temp] group by [LogFilename],[sitename],[ldate],[clientip],[iparea],[ipaddress],[stype]";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int AddRecordbyPage()
        {
            string sql = @"insert into [AnalybyPage_temp] select replace(newid(),'-',''), [LogFilename],[sitename],[ldate],[url],sum([recordcount]),[comment],[stype] from [Analy_temp] group by [LogFilename],[sitename],[ldate],[url],[comment],[stype]";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int AddRecordGeneralTemp()
        {
            string sql = "";
            int rval = 0;

            sql = "insert into [AnalybyGeneral_temp] select distinct [sitename],[ldate] from [analy_temp] group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyHitsTel_temp] select [sitename],[ldate],SUM(cast([RecordCount] as bigint)) as RecordCount from [analy_temp] where [stype]=1 group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyHitsUni_temp] select [sitename],[ldate],SUM(cast([RecordCount] as bigint)) as RecordCount from [analy_temp] where [stype]=0 group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyPageinUni_temp] select distinct [sitename],[ldate],COUNT(distinct [url]) as RecordCount from [Analy_temp] where [stype]=0 group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyPageinTel_temp] select distinct [sitename],[ldate],COUNT(distinct [url]) as RecordCount from [Analy_temp] where [stype]=1 group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyIPinUni_temp] select distinct [sitename],[ldate],COUNT(distinct [clientip]) as RecordCount from [Analy_temp] where [stype]=0 group by [sitename],[ldate]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyIPinTel_temp] select distinct [sitename],[ldate],COUNT(distinct [clientip]) as RecordCount from [Analy_temp] where [stype]=1 group by [sitename],[ldate]";
            rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int AddRecordGeneral()
        {
            string sql="";
            sql = "insert into [AnalybyIP] select * from [AnalybyIP_temp]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyPage] select * from [AnalybyPage_temp]";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "insert into [AnalybyGeneral] select replace(newid(),'-',''),a.[sitename],a.[ldate] as date,b.[RecordCount] as pvcounttel,c.[RecordCount] as pvcountuni,d.[RecordCount] as pagecounttel,e.[RecordCount] as pagecountuni,f.[RecordCount] as ipcounttel,g.[RecordCount] as ipcountuni from (select distinct [sitename],[ldate] from [AnalybyGeneral_temp]) a left join [AnalybyHitsTel_temp] b on a.[sitename]=b.[sitename] and a.[ldate]=b.[ldate] left join [AnalybyHitsUni_temp] c on a.[sitename]=c.[sitename] and a.[ldate]=c.[ldate] left join [analybyPageintel_temp] d on a.[sitename]=d.[sitename] and a.[ldate]=d.[ldate] left join [analybyPageinuni_temp] e on a.[sitename]=e.[sitename] and a.[ldate]=e.[ldate] left join [analybyipintel_temp] f on a.[sitename]=f.[sitename] and a.[ldate]=f.[ldate] left join [analybyipinuni_temp] g on a.[sitename]=g.[sitename] and a.[ldate]=g.[ldate]";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int AddPageComment(string sitename,string url,string comment)
        {
            string sql = "";
            sql = "select [sitename] from [PageComment] where [sitename]='" + sitename + "' and [uri]='" + url + "'";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            if (dt.Rows.Count > 0) return -2;
            sql = "insert into [PageComment]([sitename],[uri],[comment]) values('" + sitename + "','" + url + "','" + comment + "')";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int UpdateIPAddress(string table,IPLocation ip)
        {
            string sql = @"update [" + table + "] set [iparea]='" + ip.country + "',[ipaddress]='" + ip.area + "' where [clientip]='" + ip.ip + "'";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int UpdateServerType(string stype)
        {
            string sql = @"update [Analy_temp] set [stype]=" + stype;
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int UpdateSitename(string newid)
        {
            string sql = @"update [Analy_temp] set [sitename]='" + newid + "'";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int UpdatePageComment(string sitename, string url, string comment)
        {
            string sql = "update [pagecomment] set [comment]='" + comment + "' where [sitename]='" + sitename + "' and [uri]='" + url + "'";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int MoveRecord(string Date)
        {
            string sql = "";
            int rval = 0;
            List<string> tables = new List<string>(new string[3] { "AnalybyIP", "AnalybyPage", "AnalybyGeneral" });
            foreach (string table in tables)
            {
                sql = "insert into [" + table + "_History] from [" + table + "] where [date]<='" + Date + "'";
                if (new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql) == -1) rval = -1;
            }
            return rval;
        }

        public static int DeleteRecord(string Date)
        {
            string sql = "";
            int rval = 0;
            List<string> tables = new List<string>(new string[3] { "AnalybyIP", "AnalybyPage", "AnalybyGeneral" });
            foreach (string table in tables)
            {
                sql = "delete from [" + table + "] where [date]='" + Date + "'";
                if (new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql) == -1) rval = -1;
            }
            return rval;
        }

        public static int DeleteMultiRecord(string startDate,string endDate)
        {
            string sql = "";
            int rval = 0;
            List<string> tables = new List<string>(new string[3] { "AnalybyIP", "AnalybyPage", "AnalybyGeneral" });
            foreach (string table in tables)
            {
                sql = "delete from [" + table + "] where [date]>='" + startDate + "' and [date]<='" + endDate + "'";
                if (new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql) == -1) rval = -1;
            }
            return rval;
        }

        public static int DeletePageComment(string sitename, List<string> urls)
        {
            string sql = "delete from [pagecomment] where [sitename]='" + sitename + "' and [uri] in('" + string.Join("','", urls.ToArray()) + "')";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int ClearRecord()
        {
            int rval = 0;
            string sql = "delete from [Analy_temp] where [url] in(select [url] from [urlexclude])";
            new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            sql = "select [ldate],count(*) as dcount from [Analy_temp] group by [ldate] order by dcount desc";
            DataTable dt = new QT.DBO.SqlDBHelper().DataTable(sql);
            if (dt.Rows.Count > 0)
            {
                sql = "delete from [Analy_temp] where [ldate]<>'" + dt.Rows[0][0].ToString() + "'";
                rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            }
            return rval;
        }

        public static int DropTable()
        {
            string sql = "delete from [Analy_temp]";
            int rval = new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql);
            return rval;
        }

        public static int ClearTempRecord()
        {
            string sql = "";
            int rval = 0;
            List<string> tables = new List<string>(new string[9] { "AnalybyIP_temp", "AnalybyPage_temp", "AnalybyGeneral_temp", "AnalybyHitsTel_temp", "AnalybyHitsUni_temp", "AnalybyIPinTel_temp", "AnalybyIPinUni_temp", "AnalybyPageinTel_temp", "AnalybyPageinUni_temp" });
            foreach (string table in tables)
            {
                sql = "delete from [" + table + "]";
                if (new QT.DBO.SqlDBHelper().ExecuteNonQuery(sql) == -1) rval = -1;
            }
            return rval;
        }
    }

    public class QQWryHelper
    {
        #region Attributes

        private static FileStream ipFile;
        private static long ip;
        private static string ipfilePath;

        public string file;
        public string separator;

        private static long StartIndex = 0;
        private static long EndIndex = 0;
        private static long Count = 0;

        #endregion

        #region 构造函数

        public QQWryHelper()
        {
        }

        ///<summary> 
        /// 构造函数 
        ///</summary> 
        ///<param name="ipfilePath">纯真IP数据库路径</param>
        public QQWryHelper(string ipFilePath)
        {
            ipfilePath = ipFilePath;
        }

        #endregion

        #region CheckOut

        ///<summary> 
        /// 获取指定IP所在地理位置 
        ///</summary> 
        ///<param name="strIP">要查询的IP地址</param> 
        ///<returns></returns> 
        public IPLocation GetIPLocation(string strIP)
        {
            ip = IPToLong(strIP);
            ipFile = new FileStream(ipfilePath, FileMode.Open, FileAccess.Read);
            long[] ipArray = BlockToArray(ReadIPBlock());
            long offset = SearchIP(ipArray, 0, ipArray.Length - 1) * 7 + 4;
            ipFile.Position += offset;//跳过起始IP 
            ipFile.Position = ReadLongX(3) + 4;//跳过结束IP 

            IPLocation loc = new IPLocation();
            loc.ip = strIP;
            int flag = ipFile.ReadByte();//读取标志 
            if (flag == 1)//表示国家和地区被转向 
            {
                ipFile.Position = ReadLongX(3);
                flag = ipFile.ReadByte();//再读标志 
            }
            long countryOffset = ipFile.Position;
            loc.country = ReadString(flag);

            if (flag == 2)
            {
                ipFile.Position = countryOffset + 3;
            }
            flag = ipFile.ReadByte();
            loc.area = ReadString(flag);

            ipFile.Close();
            ipFile = null;
            return loc;
        }

        #endregion

        #region Method

        #region IP

        ///<summary> 
        /// 将字符串形式的IP转换为long 
        ///</summary> 
        ///<param name="strIP"></param> 
        ///<returns></returns> 
        private static long IPToLong(string ip_Long)
        {
            char[] separator = new char[] { '.' };
            if (ip_Long.Split(separator).Length == 3)
            {
                ip_Long = ip_Long + ".0";
            }
            string[] strArray = ip_Long.Split(separator);
            long num2 = ((long.Parse(strArray[0]) * 0x100L) * 0x100L) * 0x100L;
            long num3 = (long.Parse(strArray[1]) * 0x100L) * 0x100L;
            long num4 = long.Parse(strArray[2]) * 0x100L;
            long num5 = long.Parse(strArray[3]);
            return (((num2 + num3) + num4) + num5);
        }

        /// <summary>
        /// 将long形式的IP转换为字符串
        /// </summary>
        /// <param name="ip_Int"></param>
        /// <returns></returns>
        private static string LongToIP(long ip_Int)
        {
            long num = (long)((ip_Int & 0xff000000L) >> 0x18);
            if (num < 0L)
            {
                num += 0x100L;
            }
            long num2 = (ip_Int & 0xff0000L) >> 0x10;
            if (num2 < 0L)
            {
                num2 += 0x100L;
            }
            long num3 = (ip_Int & 0xff00L) >> 8;
            if (num3 < 0L)
            {
                num3 += 0x100L;
            }
            long num4 = ip_Int & 0xffL;
            if (num4 < 0L)
            {
                num4 += 0x100L;
            }
            return (num.ToString() + "." + num2.ToString() + "." + num3.ToString() + "." + num4.ToString());
        }

        private static string LongToString(long ip_Int)
        {
            long num = (long)((ip_Int & 0xff000000L) >> 0x18);
            if (num < 0L)
            {
                num += 0x100L;
            }
            long num2 = (ip_Int & 0xff0000L) >> 0x10;
            if (num2 < 0L)
            {
                num2 += 0x100L;
            }
            long num3 = (ip_Int & 0xff00L) >> 8;
            if (num3 < 0L)
            {
                num3 += 0x100L;
            }
            long num4 = ip_Int & 0xffL;
            if (num4 < 0L)
            {
                num4 += 0x100L;
            }
            return (num.ToString().PadLeft(3, '0') + num2.ToString().PadLeft(3, '0') + num3.ToString().PadLeft(3, '0') + num4.ToString().PadLeft(3, '0'));
        }

        #endregion

        #region Index

        ///<summary> 
        /// 将索引区字节块中的起始IP转换成Long数组 
        ///</summary> 
        ///<param name="ipBlock"></param> 
        private long[] BlockToArray(byte[] ipBlock)
        {
            long[] ipArray = new long[ipBlock.Length / 7];
            int ipIndex = 0;
            byte[] temp = new byte[8];
            for (int i = 0; i < ipBlock.Length; i += 7)
            {
                Array.Copy(ipBlock, i, temp, 0, 4);
                ipArray[ipIndex] = BitConverter.ToInt64(temp, 0);
                ipIndex++;
            }
            return ipArray;
        }

        ///<summary> 
        /// 从IP数组中搜索指定IP并返回其索引 
        ///</summary> 
        ///<param name="ipArray">IP数组</param> 
        ///<param name="start">指定搜索的起始位置</param> 
        ///<param name="end">指定搜索的结束位置</param> 
        ///<returns></returns> 
        private int SearchIP(long[] ipArray, int start, int end)
        {
            int middle = (start + end) / 2;
            if (middle == start) return middle;
            else if (ip < ipArray[middle]) return SearchIP(ipArray, start, middle);
            else return SearchIP(ipArray, middle, end);
        }

        ///<summary> 
        /// 读取IP文件中索引区块 
        ///</summary> 
        ///<returns></returns> 
        private byte[] ReadIPBlock()
        {
            StartIndex = ReadLongX(4);
            EndIndex = ReadLongX(4);
            Count = (EndIndex - StartIndex) / 7 + 1;//总记录数 
            ipFile.Position = StartIndex;
            byte[] ipBlock = new byte[Count * 7];
            ipFile.Read(ipBlock, 0, ipBlock.Length);
            ipFile.Position = StartIndex;
            return ipBlock;
        }

        #endregion

        ///<summary> 
        /// 从IP文件中读取指定字节并转换位long 
        ///</summary> 
        ///<param name="bytesCount">需要转换的字节数，主意不要超过8字节</param> 
        ///<returns></returns> 
        private long ReadLongX(int bytesCount)
        {
            byte[] _bytes = new byte[8];
            ipFile.Read(_bytes, 0, bytesCount);
            return BitConverter.ToInt64(_bytes, 0);
        }

        ///<summary> 
        /// 从IP文件中读取字符串 
        ///</summary> 
        ///<param name="flag">转向标志</param> 
        ///<returns></returns> 
        private string ReadString(int flag)
        {
            if (flag == 1 || flag == 2)//转向标志 
                ipFile.Position = ReadLongX(3);
            else
                ipFile.Position -= 1;

            List<byte> list = new List<byte>();
            byte b = (byte)ipFile.ReadByte();
            while (b > 0)
            {
                list.Add(b);
                b = (byte)ipFile.ReadByte();
            }
            return Encoding.Default.GetString(list.ToArray());
        }

        #endregion
    }

    #region Struct

    public struct IPLocation
    {
        public string ip;
        public string country;
        public string area;
    }

    public struct IPSection
    {
        public string StartIP;
        public string EndIP;
        public string Country;
        public string Area;
    }

    #endregion
}
